from typing import Dict, Any, List

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.comments import Comment
from openpyxl.worksheet.hyperlink import Hyperlink
import tools_pmc
from tqdm import tqdm


def 读取货期():
    #  92D050
    #  导出采购货期
    namecghq = "销售预测毛需求04-06月 - to采购终版.xlsx"
    ljcg = "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/"
    ljcghq = ljcg + namecghq
    wbcghq = openpyxl.load_workbook(ljcghq)
    ws_cghq = wbcghq.worksheets[0]
    list1 = [1, 1, 1, 1, 1]
    for i in range(1, ws_cghq.max_column + 1):
        if ws_cghq.cell(1, i).value is not None:
            if "首批到货数量" in ws_cghq.cell(1, i).value:
                list1[0] = i
            if "批次交期" in ws_cghq.cell(1, i).value:
                list1[1] = i
            if "下批" in ws_cghq.cell(1, i).value:
                list1[2] = i
            # if "交期变动" in ws_cghq.cell(1, i).value:
            #     list1[3] = i
            if "情况" in ws_cghq.cell(1, i).value:
                list1[4] = i
    print("货期信息位置：", list1)

    wb_info = openpyxl.load_workbook(tools_pmc.lj_info)
    wb_info.create_sheet("采购货期反馈" + tools_pmc.today_str1, 0)
    ws = wb_info.worksheets[0]
    a = 2
    for i in range(2, ws_cghq.max_row + 1):
        ws.cell(a, 1).value = str(ws_cghq.cell(i, 1).value)
        ws.cell(a, 2).value = str(ws_cghq.cell(i, 2).value)
        ws.cell(a, 3).value = str(ws_cghq.cell(i, 3).value)
        if ws_cghq.cell(i, list1[1]).value is not None and ws_cghq.cell(i, list1[1]).fill.fgColor.index[2:] != "92D050":
            ws.cell(a, 4).value = ws_cghq.cell(i, list1[0]).value  # 首批到货数量
            try:
                ws.cell(a, 5).value = ws_cghq.cell(i, list1[1]).value.strftime("%Y/%m/%d")
            except:
                if type(ws_cghq.cell(i, list1[1]).value) == int:
                    ws.cell(a, 5).value = tools_pmc.detect_date_int(ws_cghq.cell(i, list1[1]).value)
                else:
                    ws.cell(a, 5).value = tools_pmc.detect_date_str(ws_cghq.cell(i, list1[1]).value)
        ws.cell(a, 6).value = ws_cghq.cell(i, list1[2]).value  # 下批到货数量/时间
        if ws_cghq.cell(i, list1[3]).value is not None:
            temp1 = ws_cghq.cell(i, list1[3]).value
        else:
            temp1 = ""
        if ws_cghq.cell(i, list1[4]).value is not None:
            temp2 = ws_cghq.cell(i, list1[4]).value
        else:
            temp2 = ""
        ws.cell(a, 7).value = temp2
        a += 1

    list1 = ['品号', '品名', '规格', '首批到货数量', '批次交期', '下批到货数量/时间', '情况说明']
    list2 = [14, 35, 35, 8, 14, 30, 20]
    for i in range(1, 8):
        ws.cell(1, i).value = list1[i - 1]
        ws.column_dimensions[get_column_letter(i)].width = list2[i - 1]

    wb_info.save(tools_pmc.lj_info)
    return ws


def 导入拼版数据():
    wb_data = openpyxl.load_workbook(tools_pmc.lj_data, data_only=True)
    ws_data_pb = wb_data.worksheets[1]  # 拼版数据

    table_array_data_pb = list(ws_data_pb['A1':'I' + str(ws_data_pb.max_row)])
    for i in range(1, ws.max_column + 1):
        if ws.cell(2, i).value is not None:
            if str(ws.cell(2, i).value)[0] == "3":
                temp = tools_pmc.vlookup_pb(ws.cell(2, i).value[0:12], table_array_data_pb)
                if temp is not None:
                    comment = Comment(tools_pmc.str_pb(temp), 'shen')
                    comment.width = 150
                    comment.height = 50
                    ws.cell(2, i).comment = comment


def 物料信息截取行():
    for i in table_array_wlxxb:
        if str(i[0].value)[0:2] == "11":
            return table_array_wlxxb.index(i)


ws_cghq = 读取货期()
dict_bom = tools_pmc.Y状态BOM尾阶用量_pro()[0]

wb = openpyxl.Workbook()
ws = wb.worksheets[0]
ws.title = "成品BOM信息表"

table_array_wlxxb = tools_pmc.table_array_wlxxb
temp_物料信息截取行 = 物料信息截取行()

a = 2
for i in table_array_wlxxb[0:temp_物料信息截取行]:
    for j in range(0, 15):
        ws.cell(a, j + 1).value = i[j].value
    a += 1

for i in tqdm(dict_bom, desc="生成BOM表格"):
    规格包含品号数 = len(dict_bom[i][0])
    规格开始列 = ws.max_column + 1
    ws.cell(1, 规格开始列).value = i
    ws.cell(1, 规格开始列).fill = (
        tools_pmc.fill_blue if (list(dict_bom.keys()).index(i) & 1) == 0 else tools_pmc.fill_orange)
    ws.cell(1, 规格开始列).alignment = tools_pmc.alignment_center
    ws.merge_cells(get_column_letter(规格开始列) + "1:" + get_column_letter(规格开始列 + 规格包含品号数 * 2 - 1) + "1")

    a = 0
    for j in dict_bom[i][0]:
        if dict_bom[i][1][a] != "":
            ws.cell(2, 规格开始列 + a * 2).value = j + "\n" + dict_bom[i][1][a]
        else:
            ws.cell(2, 规格开始列 + a * 2).value = j
        ws.cell(2, 规格开始列 + a * 2).alignment = tools_pmc.alignment_center
        ws.cell(2, 规格开始列 + a * 2 + 1).value = 0
        ws.cell(2, 规格开始列 + a * 2 + 1).alignment = tools_pmc.alignment_center
        ws.column_dimensions[get_column_letter(规格开始列 + a * 2)].width = 15
        ws.column_dimensions[get_column_letter(规格开始列 + a * 2 + 1)].width = 7
        for k in range(3, ws.max_row + 1):
            if ws.cell(k, 1).value in dict_bom[i][2][a]:
                ws.cell(k, 规格开始列 + a * 2).value = dict_bom[i][2][a][ws.cell(k, 1).value]
                ws.cell(k, 规格开始列 + a * 2 + 1).value = (
                        "=PRODUCT(" + get_column_letter(规格开始列 + a * 2) + str(k) +
                        "," + get_column_letter(规格开始列 + a * 2 + 1) + "2)")
        a += 1


def 并存合计函数(行: int, offset: int):
    temp = 17 + (offset - 1) * 490
    temp1 = ""
    while temp - 16 < 490 * offset and temp <= max_column:
        temp1 += get_column_letter(temp) + str(行) + ","
        temp += 2
    return "=SUM(" + temp1[:-1] + ")"


max_column = ws.max_column
并存合计分解数 = int((max_column - 15) / 490) + 1
for i in tqdm(range(3, ws.max_row + 1), desc="合计/缺料计算"):
    for j in range(1, 并存合计分解数 + 1):
        ws.cell(i, max_column + j).value = 并存合计函数(i, j)
        ws.cell(2, max_column + j).value = "并存合计" + str(j)
    ws.cell(i, max_column + 并存合计分解数 + 1).value = "=SUM(" + get_column_letter(max_column + 1) + str(
        i) + ":" + get_column_letter(max_column + 并存合计分解数) + str(i) + ")"
    ws.cell(i, max_column + 并存合计分解数 + 2).value = "=O" + str(i) + "-" + get_column_letter(
        max_column + 并存合计分解数 + 1) + str(i)
    ws.cell(2, max_column + 并存合计分解数 + 1).value = "并存合计"
    ws.cell(2, max_column + 并存合计分解数 + 2).value = "缺料统计"
    ws.cell(2, max_column + 并存合计分解数 + 1).fill = tools_pmc.fill_orange
    ws.cell(2, max_column + 并存合计分解数 + 2).fill = tools_pmc.fill_yellow
    ws.cell(2, max_column + 并存合计分解数 + 1).alignment = tools_pmc.alignment_center
    ws.cell(2, max_column + 并存合计分解数 + 2).alignment = tools_pmc.alignment_center

a = 2
max_column = ws.max_column
for i in tqdm(table_array_wlxxb[0:temp_物料信息截取行], desc="物料其他信息"):
    for j in range(15, 34):
        ws.cell(a, max_column + j - 14).value = i[j].value
    a += 1

max_column = ws.max_column
table_array_cghq = list(ws_cghq['A1':'G' + str(ws_cghq.max_row)])
for i in tqdm(range(3, (ws.max_row + 1)), desc="导入货期信息"):
    ws.cell(i, max_column + 1).value = tools_pmc.vlookup(ws.cell(i, 1).value, table_array_cghq, 4)
    ws.cell(i, max_column + 2).value = tools_pmc.vlookup(ws.cell(i, 1).value, table_array_cghq, 5)
    ws.cell(i, max_column + 3).value = tools_pmc.vlookup(ws.cell(i, 1).value, table_array_cghq, 6)
    ws.cell(i, max_column + 4).value = tools_pmc.vlookup(ws.cell(i, 1).value, table_array_cghq, 7)

导入拼版数据()

# 格式调整

ws.row_dimensions[1].height = 24
ws.row_dimensions[2].height = 54
for i in range(3, ws.max_row + 1):
    ws.row_dimensions[i].height = 13.5
    ws.cell(i, 3).alignment = tools_pmc.alignment_left
    ws.cell(i, 7).alignment = tools_pmc.alignment_left
    ws.cell(i, 1).data_type = "int"
    ws.cell(i, 1).number_format = "0"
    if ws.cell(i, 5).value is not None and (ws.cell(i, 5).value[0] == "1" or ws.cell(i, 5).value[0] == "2"):
        ws.cell(i, 5).data_type = "int"
        ws.cell(i, 5).number_format = "0"
    ws.cell(i, 1).alignment = tools_pmc.alignment_left
    ws.cell(i, 5).alignment = tools_pmc.alignment_left

ws.freeze_panes = 'P3'
ws.auto_filter.ref = "A2:" + get_column_letter(ws.max_column) + str(ws.max_row)
ws.views.sheetView[0].zoomScale = 90

list1 = [13, 26, 26, 0.0001, 0.0001, 13, 8.7, 8.7, 8.7, 0.0001, 0.0001, 0.0001, 0.0001, 8.7, 8.7, 8.7, 8.7]
for i in range(1, 16):
    ws.column_dimensions[get_column_letter(i)].width = list1[i - 1]
    ws.cell(2, i).fill = tools_pmc.fill_blue
    ws.cell(2, i).alignment = tools_pmc.alignment_center

ws.cell(2, 15).fill = tools_pmc.fill_yellow
ws.cell(2, 14).fill = tools_pmc.fill_orange

ws.merge_cells("N1:O1")
ws["N1"].value = "点击跳转至货期"
ws["N1"].font = tools_pmc.font_超链接
ws["N1"].hyperlink = Hyperlink(ref='A1', location="成品BOM信息表!" + get_column_letter(ws.max_column - 1) + "1")
ws["N1"].fill = tools_pmc.fill_green
ws["N1"].alignment = tools_pmc.alignment_center

list1 = ["A1", "C1"]
list2 = ["成品BOM信息表" + tools_pmc.today_str1, "信息结存时间：" + tools_pmc.real_yesterday.strftime("%Y.%m.%d")]
for i in range(len(list1)):
    ws[list1[i]].value = list2[i]
    ws[list1[i]].alignment = tools_pmc.alignment_lcf

list1 = ['首批到货数量', '批次交期', '下批到货数量/时间', '情况说明']
list2 = [8, 14, 30, 20]
list3 = [max_column + 1, max_column + 2, max_column + 3, max_column + 4]
for i in range(0, 4):
    ws.cell(2, list3[i]).value = list1[i]
    ws.column_dimensions[get_column_letter(list3[i])].width = list2[i]
    ws.cell(2, list3[i]).alignment = tools_pmc.alignment_center
    ws.cell(2, list3[i]).fill = tools_pmc.fill_green

for i in range(ws.max_column - 22, ws.max_column - 3):
    ws.cell(2, i).fill = tools_pmc.fill_blue
    ws.cell(2, i).alignment = tools_pmc.alignment_center

for i in range(ws.max_column - 19, ws.max_column - 3):
    ws.column_dimensions[get_column_letter(i)].width = 0.0001
for i in range(ws.max_column - 24 - 并存合计分解数, ws.max_column - 24):
    ws.column_dimensions[get_column_letter(i)].width = 0.0001

wb.save(tools_pmc.lj_CBOM_新)
print(tools_pmc.today_str1 + ":BOM succeed!")
